{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "international-vietnamese",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import pickle\n",
    "\n",
    "\n",
    "%config Completer.use_jedi = False\n",
    "\n",
    "joinpath = os.path.join\n",
    "\n",
    "os.chdir('/Users/dp/Nova/NovaGIT/DataVisDreamDest/')\n",
    "os.getcwd()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "absent-crisis",
   "metadata": {},
   "outputs": [],
   "source": [
    "rawdatainfo = pd.read_csv('datamanager/overview_rawdata.csv', sep=';')\n",
    "rawdatainfo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "young-service",
   "metadata": {},
   "outputs": [],
   "source": [
    "file = 'data/computed_data/city_codes.pickle'\n",
    "with open(file, 'rb') as f:\n",
    "    city_codes = pickle.load(f)\n",
    "print(city_codes.isna().sum())\n",
    "\n",
    "city_codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "electoral-mason",
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_data_path = 'data/raw_data/'\n",
    "dimensions_path = os.path.join(raw_data_path, 'dimensions')\n",
    "\n",
    "!ls {raw_data_path}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "statistical-madness",
   "metadata": {},
   "outputs": [],
   "source": [
    "!head {os.path.join(dimensions_path,'Index.csv ') }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "sixth-pressure",
   "metadata": {},
   "outputs": [],
   "source": [
    "for file in os.listdir(dimensions_path): \n",
    "    print('\\n', file)\n",
    "    !head {os.path.join(dimensions_path, file) }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "known-browser",
   "metadata": {},
   "outputs": [],
   "source": [
    "rawdatainfo = pd.read_csv(joinpath('datamanager/overview_rawdata.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "flying-quick",
   "metadata": {},
   "outputs": [],
   "source": [
    "citytemp = pd.read_csv('data/raw_data/dimensions/city_temperature_2020.csv')\n",
    "citytemp.rename(columns={'City': 'city'}, inplace=True)\n",
    "res = citytemp.merge(city_codes, how='left', on='city')\n",
    "\n",
    "#if res.citycode.isna().sum() != 0: \n",
    "res[res.city_code.isna()].city.unique()\n",
    "\n",
    "city_codes[city_codes.city.str.contains('Berlin')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "sustained-blind",
   "metadata": {},
   "outputs": [],
   "source": [
    "country_path = 'data/computed_data/country_codes.pickle'\n",
    "with open(country_path, 'rb') as f: \n",
    "    country = pickle.load(f)\n",
    "print(country.isna().sum())\n",
    "country\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "protective-philip",
   "metadata": {},
   "outputs": [],
   "source": [
    "country_meta = {\n",
    "    'technical': \n",
    "}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "modified-bahamas",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#cities from pickle\n",
    "\n",
    "cities_path = 'data/computed_data/city_codes.pickle'\n",
    "with open(cities_path, 'rb') as f: \n",
    "    cities = pickle.load(f)\n",
    "print(cities.isna().sum())\n",
    "cities\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "crazy-collar",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "threatened-collect",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "expanded-journey",
   "metadata": {},
   "outputs": [],
   "source": [
    "city_codes[city_codes.city.str.contains('Cairo', case=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "applied-island",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "impressive-ranking",
   "metadata": {},
   "outputs": [],
   "source": [
    "cities.cc_code.str.endswith('-X').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "acting-delta",
   "metadata": {},
   "outputs": [],
   "source": [
    "cities[cities.city_code.isna()].sort_values('population', ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "liable-royalty",
   "metadata": {},
   "outputs": [],
   "source": [
    "#cities[cities.city_code.str.contains('CN-WEI', case=False)]\n",
    "cities[cities.cc_code == 'GB-STK']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "retained-inquiry",
   "metadata": {},
   "outputs": [],
   "source": [
    "cities.sort_values('population', ascending=False).head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "regulation-latter",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datamanager import processing\n",
    "\n",
    "numdata = pd.DataFrame(columns=['location_name', 'location_type'])\n",
    "\n",
    "\n",
    "for idx, row in rawdatainfo.iterrows():\n",
    "    print(f'Processing file {filename}')\n",
    "\n",
    "    \n",
    "    #\n",
    "    cleaner = globals()[f'clean_{row.dataset}']\n",
    "\n",
    "    \n",
    "    # read file\n",
    "    file_raw = pd.read_csv()\n",
    "    \n",
    "    # apply cleaning\n",
    "    file = cleaner(file_raw)\n",
    "    \n",
    "    # join \n",
    "    numdata.merge(how='outer')\n",
    "    print(idx, row)\n",
    "    #print(datafile.name[idx])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "increasing-extreme",
   "metadata": {},
   "source": [
    "## country level "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "classical-brooks",
   "metadata": {},
   "outputs": [],
   "source": [
    "country_num = country.copy()\n",
    "country_num"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "becoming-lucas",
   "metadata": {},
   "outputs": [],
   "source": [
    "age_file = 'data/raw_data/dimensions/age_group.csv'\n",
    "\n",
    "age = pd.read_csv(age_file)\n",
    "\n",
    "age.rename(\n",
    "    columns={\n",
    "        'Country': 'country',\n",
    "        'Under 5': 'babies',\n",
    "        'Aged 5-14': 'children',\n",
    "        'Aged 15-24': 'adolescence',\n",
    "        'Aged 25-64': 'adults',\n",
    "        'Over 65': 'elderly'},\n",
    "    inplace=True)\n",
    "\n",
    "age\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "conditional-violation",
   "metadata": {},
   "outputs": [],
   "source": [
    " \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "varied-footwear",
   "metadata": {},
   "outputs": [],
   "source": [
    "age.country_code.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "legislative-vienna",
   "metadata": {},
   "outputs": [],
   "source": [
    "indices_file = 'data/raw_data/dimensions/Index.csv'\n",
    "indices = pd.read_csv(indices_file)\n",
    "indices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "straight-father",
   "metadata": {},
   "outputs": [],
   "source": [
    "wdi_file =  'data/raw_data/dimensions/WDICountry.csv'\n",
    "\n",
    "wdi = pd.read_csv(wdi_file)\n",
    "# basic cleaning\n",
    "\n",
    "wdi.rename(columns={'2-alpha code': 'country_code', 'Currency Unit': 'currency', 'Region': 'region', 'Income Group': 'income'}, inplace=True)\n",
    "\n",
    "print('Income: \\n', wdi['income'].value_counts())\n",
    "\n",
    "print('\\nRegion: \\n',wdi['region'].value_counts())\n",
    "\n",
    "print('\\nCurrency: \\n',wdi['currency'].value_counts())\n",
    "\n",
    "wdi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "confused-crawford",
   "metadata": {},
   "outputs": [],
   "source": [
    "wdi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "governmental-kingdom",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "whr_file = 'data/raw_data/dimensions/whr.csv'\n",
    "whr = pd.read_csv(whr_file)\n",
    "\n",
    "print(whr.describe())\n",
    "\n",
    "whr"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "divided-estonia",
   "metadata": {},
   "source": [
    "## city level"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "juvenile-aruba",
   "metadata": {},
   "source": [
    "### cost of living"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "settled-haiti",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "col_file = 'data/raw_data/dimensions/col.csv'\n",
    "\n",
    "col = pd.read_csv(col_file)\n",
    "col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "hundred-poland",
   "metadata": {},
   "outputs": [],
   "source": [
    "weather_file = 'data/raw_data/dimensions/weather.csv'\n",
    "\n",
    "weather = pd.read_csv(weather_file)\n",
    "weather\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "alleged-uzbekistan",
   "metadata": {},
   "source": [
    "# to binary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "smaller-samba",
   "metadata": {},
   "outputs": [],
   "source": [
    "num_data_file = 'data_bool_geo_final.csv'\n",
    "num_data = pd.read_csv(num_data_file)\n",
    "num_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aboriginal-syndrome",
   "metadata": {},
   "outputs": [],
   "source": [
    "id_cols = ['City Code', 'Country Code']\n",
    "\n",
    "geodata_to_join = cities[['city_code', 'country_code', 'lat', 'lng']].rename(columns={'city_code':'City Code', 'country_code': 'Country Code'})\n",
    "\n",
    "num_data_geo = num_data.merge(geodata_to_join, how='left', on=['City Code', 'Country Code'])\n",
    "\n",
    "obs_per_id = num_data_geo.groupby(id_cols).size()\n",
    "\n",
    "obs_per_id[obs_per_id != 1]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "orange-village",
   "metadata": {},
   "outputs": [],
   "source": [
    "num_data_geo.to_csv('data/computed_data/inner_join_geo.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "challenging-trinity",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "def make_binary(X):\n",
    "    X['upper_quart'] = [1 if x > X.quantile(.75) else 0 for x in X]\n",
    "    X['lower_quart'] = [1 if x < X.quantile(.25) else 0 for x in X]\n",
    "\n",
    "    #bin_low = x[x<x.quantile(.25)]\n",
    "    #return pd.Series({'low': lower_quart, 'high': upper_quart})\n",
    "    #return pd.Series({'high': upper_quart}).transpose()\n",
    "    return  X\n",
    "\n",
    "\n",
    "\n",
    "#make_binary(num_data.Generosity)\n",
    "#num_data[['Social support', 'Healthy life expectancy']].apply(make_binary, axis=0)\n",
    "\n",
    "def make_binary(X):\n",
    "    upper_quart = [1 if x > X.quantile(.75) else 0 for x in X]\n",
    "    lower_quart = [1 if x < X.quantile(.25) else 0 for x in X]\n",
    "\n",
    "    #bin_low = x[x<x.quantile(.25)]\n",
    "    return pd.Series({'low': lower_quart, 'high': upper_quart})\n",
    "    #return pd.Series({'high': upper_quart}).transpose()\n",
    "    return  X"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "intimate-france",
   "metadata": {},
   "outputs": [],
   "source": [
    "def data_to_bool(num_data): \n",
    "\n",
    "    tech_cols = ['City Code', 'Country Code', 'City', 'Country', 'lat', 'lng']\n",
    "    #cols_to_binary = num_data.columns.difference(tech_cols)\n",
    "    cols_to_binary = num_data.columns[~num_data.columns.isin(tech_cols)]\n",
    "\n",
    "\n",
    "    res = num_data[tech_cols]\n",
    "    for col in num_data[cols_to_binary]:\n",
    "        X = num_data[col]\n",
    "        res_tmp = pd.DataFrame({\n",
    "            f'high {col.lower()}':[True if x > X.quantile(.75) else False for x in X],\n",
    "            f'low {col.lower()}':[True if x < X.quantile(.25) else False for x in X]\n",
    "        })\n",
    "\n",
    "        #upper_quart = [1 if x > X.quantile(.75) else 0 for x in X]\n",
    "        #lower_quart = [1 if x < X.quantile(.25) else 0 for x in X]\n",
    "\n",
    "        #res = res.merge(pd.Series({'upper': upper_quart}), left_index=True, right_index=True)\n",
    "        res = res.merge(res_tmp, left_index=True, right_index=True)\n",
    "\n",
    "    return res\n",
    "\n",
    "def check_bool_data(num_data, bool_data): \n",
    "    res = all(num_data[num_data['Logged GDP per capita'] > num_data['Logged GDP per capita'].quantile(.75)].index \n",
    "    == bool_data[bool_data['high logged gdp per capita']].index)\n",
    "    \n",
    "    if res: \n",
    "        print('ok')\n",
    "    else: \n",
    "        raise Exception()\n",
    "        \n",
    "\n",
    "\n",
    "bool_data_geo = data_to_bool(num_data_geo)\n",
    "check_bool_data(num_data_geo, bool_data_geo)\n",
    "\n",
    "bool_data_geo.to_csv('data/computed_data/data_bool_geo.csv')\n",
    "bool_data_geo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fifteen-conditioning",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "hawaiian-alliance",
   "metadata": {},
   "outputs": [],
   "source": [
    "filters_list = ['high logged gdp per capita', 'high social support']\n",
    "\n",
    "def apply_filters(df, attributes): \n",
    "    filters = []\n",
    "    for f in attributes: \n",
    "        filters.append(df[f])\n",
    "\n",
    "    #res[res['high logged gdp per capita']]\n",
    "    return df.loc[np.all(filters, 0), ['City Code', 'Country Code']]\n",
    "\n",
    "apply_filters(res, filters_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "anonymous-picture",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "occupied-rider",
   "metadata": {},
   "outputs": [],
   "source": [
    "all(num_data[num_data['Logged GDP per capita'] > num_data['Logged GDP per capita'].quantile(.75)].index \n",
    "    == bool_data_geo[bool_data_geo['high logged gdp per capita']].index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "resident-packing",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import pickle\n",
    "\n",
    "\n",
    "%config Completer.use_jedi = False\n",
    "\n",
    "joinpath = os.path.join\n",
    "\n",
    "os.chdir('/Users/dp/Nova/NovaGIT/DataVisDreamDest/')\n",
    "os.getcwd()\n",
    "\n",
    "from helper import plots\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "subject-brand",
   "metadata": {},
   "outputs": [],
   "source": [
    "plots.custom_dims_plot(\n",
    "    location='Istanbul',\n",
    "    dims_selected=['Beer', 'Health'],\n",
    "    city_info_num=city_info_num,\n",
    "    city_info_num_agg=city_info_num_agg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "freelance-indonesian",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import plotly.graph_objects as go\n",
    "from matplotlib import pyplot as plt\n",
    "import numpy as np\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "city_info_num = pd.read_csv('df_final.csv')\n",
    "city_info_num.head()\n",
    "city_info_num.dtypes\n",
    "city_info_num_agg = city_info_num.median(axis=1)#.reset_index()\n",
    "city_info_num_agg.head()\n",
    "city_info_num_agg.shape\n",
    "\n",
    "\n",
    "city_info_num_agg = city_info_num.drop(columns=['City', 'Country']).apply(np.median)\n",
    "\n",
    "city_info_num_agg#[['Beer']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "indie-tourist",
   "metadata": {},
   "outputs": [],
   "source": [
    "def custom_dims_plot(location, dims_selected):\n",
    "\n",
    "    vals_city = city_info_num.loc[city_info_num['City'] == location, dims_selected].values.tolist()[0]\n",
    "    vals_agg = city_info_num_agg[dims_selected].values.tolist()\n",
    "    \n",
    "\n",
    "\n",
    "    fig = go.Figure()\n",
    "\n",
    "    fig.add_trace(go.Bar(\n",
    "        y=dims_selected,\n",
    "        x=vals_agg,\n",
    "        name='Meannn',\n",
    "        orientation='h',\n",
    "        marker=dict(\n",
    "            color='rgba(58, 71, 80, 0.6)'\n",
    "                     )\n",
    "        )\n",
    "    )\n",
    "    fig.add_trace(go.Bar(\n",
    "        y=dims_selected,\n",
    "        x=vals_city,\n",
    "        name='Selected',\n",
    "        orientation='h',\n",
    "        marker=dict(\n",
    "            color='rgba(246, 78, 139, 0.6)'\n",
    "                     )\n",
    "        )\n",
    "    )\n",
    "\n",
    "    fig.update_layout(barmode='group')\n",
    "    return fig\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "junior-grill",
   "metadata": {},
   "outputs": [],
   "source": [
    "custom_dims_plot(location='Istanbul', dims_selected=['Beer', 'Health'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "threaded-contrary",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "city_info_num\n",
    "org_colnames = city_info_num.drop(columns=[\"City\", \"Country\", \"Lat\", \"Long\"]).columns\n",
    "mapping_dict = dict(zip(\n",
    "    map(str.lower, org_colnames), \n",
    "    [('name', 'high', 'low') for _ in range(len(org_colnames))]\n",
    "\n",
    "))\n",
    "\n",
    "with open('mapping_dict.json', 'w') as f: \n",
    "    json.dump(mapping_dict,f, indent=4) \n",
    "mapping_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "sized-lecture",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('mapping_dict_final.json', 'r') as f: \n",
    "    mapping_dict_final = json.load(f) \n",
    "#mapping_dict_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "separate-grocery",
   "metadata": {},
   "outputs": [],
   "source": [
    "mapping_dict_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "musical-naples",
   "metadata": {},
   "outputs": [],
   "source": [
    "mapping_high = {f'high {k}': f'{v[1]} {v[0]}' for k,v in mapping_dict_final.items()}\n",
    "mapping_low = {f'low {k}': f'{v[2]} {v[0]}' for k,v in mapping_dict_final.items()}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "affecting-boston",
   "metadata": {},
   "outputs": [],
   "source": [
    "mapping_high.update(mapping_low)\n",
    "mapping_high"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "popular-memorial",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('mapping_dict_final_binary.json', 'w') as f: \n",
    "    json.dump(mapping_high,f, indent=4) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "completed-soviet",
   "metadata": {},
   "outputs": [],
   "source": [
    "mapping_basedims = {k: v[0] for k,v in mapping_dict_final.items()}\n",
    "with open('mapping_dict_final.json', 'w') as f: \n",
    "    json.dump(mapping_basedims,f, indent=4)\n",
    "\n",
    "mapping_basedims\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
